/* For detailed instructions, see README file */


OPTIONS SOURCE MACROGEN SYMBOLGEN MPRINT COMPRESS=YES REUSE=YES SORTSIZE= MAX;




/*=========================================*/
/*Select path where files are saved*/
libname OP "INSERT PATH";
/*Select path to save logs*/
%let LOG_PATH=INSERT PATH\logs\Table3_4.log;
options dlcreatedir; 
%PUT &LOG_PATH;
/*=========================================*/




proc printto log="&LOG_PATH" new;
run;




ODS GRAPHICS OFF;
ODS NORESULTS;
ODS SELECT NONE;
FOOTNOTE " ";
ODS TITLE " ";
/*=========================================*/

data Option_data;
	SET OP.OPRA_Pseudo;


	
	IF DMMP_IND=1 THEN
	DIRECTED_OF=PUT("YES",$32.);
	ELSE DIRECTED_OF=PUT("NO",$32.);






	IF AUCTION_IND=1 THEN AUCTION=PUT("Yes",$3.); else AUCTION=PUT("No",$3.);




	
	/*nyse america*/
	IF sender="A" or
	/*CBOE*/
	sender="C" or
	/*Edge*/
	sender="E" or
	/*ice*/
	sender="I" or
	/*ARCA*/
	sender="N" or
	/*MIAX*/
	sender="M" or
	/*PHLX*/
	sender="X"
	
	then
	PFOF_Model=PUT("Yes",$3.);
	ELSE PFOF_Model=PUT("No",$3.);


	IF AUCTION_IND=1 THEN AUCTION=PUT("Yes",$3.); else AUCTION=PUT("No",$3.);




		IF SENDER="A" 
		THEN MARKET=PUT("AMEX",$28.);

		ELSE IF SENDER="B" 
		THEN MARKET=PUT("BOX",$28.);

		ELSE IF SENDER="C" 
		THEN MARKET=PUT("CBOE",$28.);

		ELSE IF SENDER="D" 
		THEN MARKET=PUT("EMERALD",$28.);

		ELSE IF SENDER="E" 
		THEN MARKET=PUT("EDGX",$28.);

		ELSE IF SENDER="H" 
		THEN MARKET=PUT("GEMX",$28.);

	
		ELSE IF SENDER="I" 
		THEN MARKET=PUT("ISE",$28.);

		ELSE IF SENDER="J" 
		THEN MARKET=PUT("MRX",$28.);


		ELSE IF SENDER="M" 
		THEN MARKET=PUT("MIAX",$28.);


		ELSE IF SENDER="N" 
		THEN MARKET=PUT("ARCA",$28.);



		ELSE IF SENDER="O" 
		THEN MARKET=PUT("OPRA",$28.);


		ELSE IF SENDER="P" 
		THEN MARKET=PUT("PEARL",$28.);



		ELSE IF SENDER="Q" 
		THEN MARKET=PUT("NOM",$28.);

		ELSE IF SENDER="W" 
		THEN MARKET=PUT("Cboe C2",$28.);

		ELSE IF SENDER="T" 
		THEN MARKET=PUT("BX",$28.);

		ELSE IF SENDER="X" 
		THEN MARKET=PUT("PHLX",$28.);

		ELSE IF SENDER="Z" 
		THEN MARKET=PUT("BATS",$28.);

		ONES=1;

		IF AUCTION_IND=1 THEN Type=put("Auc.",$3.); else Type=put("LOB",$3.);

	RUN;









/*MARKET VOLUME*/

proc tabulate data=Option_data format=8.2 OUT=PCTSUM_TRADES_VOL;
class MARKET;
VAR D_VOL;
table  MARKET*D_VOL*(PCTSUM);
 RUN;

 
 PROC SORT DATA=Option_data; BY AUCTION;RUN;
proc tabulate data=Option_data format=8.2 OUT=PCTSUM_TRADES_VOL_BYTYPE;
class MARKET TYPE;
VAR D_VOL;
table  MARKET*TYPE*D_VOL*(PCTSUM);
 RUN;

 DATA PCTSUM_TRADES_VOL_BYTYPE;
 	SET PCTSUM_TRADES_VOL_BYTYPE;
	DROP _PAGE_ _TYPE_ _TABLE_;
	RENAME D_VOL_PctSum_00_D_VOL = PCTSUM;
RUN;

 PROC SORT DATA= PCTSUM_TRADES_VOL_BYTYPE; BY MARKET;RUN;
 PROC TRANSPOSE DATA=PCTSUM_TRADES_VOL_BYTYPE OUT=PCTSUM_TRADES_VOL_BYTYPE_T;BY MARKET;ID TYPE;RUN;

 DATA PCTSUM_TRADES_VOL_BYTYPE_T;
 	SET PCTSUM_TRADES_VOL_BYTYPE_T;
	DROP _NAME_;
RUN;


 /*MARKET SPREADS*/
 
 
proc tabulate data=Option_data(WHERE=(AUCTION="Yes")) format=8.2 OUT=MEAN_SPREADS_AUCTION
(
RENAME=(


QUOTEDSPREAD_C_Mean=QUOTEDSPREAD_C
EFFECTIVESPREAD_C_Mean= EFFECTIVESPREAD_C
PIMP_C_Mean= PIMP_C
EQ_Mean= EQ

)
)




;
class MARKET;
VAR EQ QUOTEDSPREAD_C EFFECTIVESPREAD_C PIMP_C ;
table  
	    MARKET*EQ*(MEAN)
	    MARKET*QuotedSpread_C*(MEAN)
	    MARKET*EffectiveSpread_C*(MEAN)
	    MARKET*PIMP_C*(MEAN)



	   ;
 RUN;



 

ODS RESULTS OFF;
proc tabulate data=Option_data(WHERE=(AUCTION="No")) format=8.2 OUT=MEAN_SPREADS_LOB
(
RENAME=(

QUOTEDSPREAD_C_Mean=QUOTEDSPREAD_C
EFFECTIVESPREAD_C_Mean= EFFECTIVESPREAD_C
PIMP_C_Mean= PIMP_C
EQ_Mean= EQ
)
)




;
class MARKET;
VAR EQ QUOTEDSPREAD_C EFFECTIVESPREAD_C PIMP_C ;

table  

		MARKET*EQ*(MEAN)
	    MARKET*QuotedSpread_C*(MEAN)
	    MARKET*EffectiveSpread_C*(MEAN)
	    MARKET*PIMP_C*(MEAN)




	   ;
 RUN;



proc tabulate data=Option_data format=8.2 OUT=EXCH_TYPE_COUNT;
class MARKET TYPE ;
VAR ONES;
tables 
MARKET*Type*ONES*(SUM);
 RUN;
 PROC SORT DATA=EXCH_TYPE_COUNT; BY MARKET TYPE;RUN;
DATA EXCH_TYPE_COUNT;
	SET EXCH_TYPE_COUNT;
	DROP _PAGE_ _TYPE_ _TABLE_ _NAME_;
RUN;
proc transpose data=EXCH_TYPE_COUNT out=EXCH_TYPE_COUNT_T; BY MARKET; ID TYPE;RUN;

DATA EXCH_TYPE_COUNT_T;
	SET EXCH_TYPE_COUNT_T;

	IF AUC=. THEN AUC=0;
	
	DROP _NAME_;

RUN;

PROC SORT DATA=Option_data;BY TYPE;RUN;

proc tabulate data=Option_data format=8.2 OUT=EXCH_TYPE_PERC;
class MARKET ;
VAR ONES;
tables 
MARKET*ONES*(PCTSUM);

BY TYPE;
 RUN;

PROC SORT DATA=EXCH_TYPE_PERC; BY MARKET;RUN;
DATA EXCH_TYPE_PERC;
	SET EXCH_TYPE_PERC;
	DROP _PAGE_ _TYPE_ _TABLE_ _NAME_;
RUN;
proc transpose data=EXCH_TYPE_PERC out=EXCH_TYPE_PERC_T; BY  MARKET; ID TYPE;RUN;


DATA EXCH_TYPE_PERC_T;
	SET EXCH_TYPE_PERC_T;

	IF AUC=. THEN AUC=0;

	RENAME AUC=AUC_P
	LOB=LOB_P;

	DROP _NAME_;
RUN;
PROC SQL;
	CREATE TABLE EXCH_TYPE
	AS SELECT A.*,B.*
	FROM EXCH_TYPE_COUNT_T A LEFT JOIN EXCH_TYPE_PERC_T B
	ON A.MARKET=B.MARKET;
QUIT;






PROC SORT NODUPKEY data=OPTION_DATA out=EXCHANGES_LABEL (KEEP=SENDER MARKET DMMP_IND); BY MARKET;RUN;
/*COMBINNING ALL*/
PROC SQL;
	CREATE TABLE EXCH_DATA
	AS SELECT
	A.*,
 
B.EQ AS EQ_LOB,
B.QUOTEDSPREAD_C as QS_C_LOB, B.EffectiveSpread_C AS ES_C_LOB, B.PIMP_C AS PIMPR_C_LOB, 


G.EQ AS EQ_AUC,
G.QUOTEDSPREAD_C as QS_C_AUC, G.EffectiveSpread_C AS ES_C_AUC, G.PIMP_C AS PIMPR_C_AUC, 




C.D_VOL_PctSum_0_D_VOL AS PERC_VOL,

F.SENDER,F.DMMP_IND,

H.AUC AS PCTSUM_AUC,
H.LOB AS PCTSUM_LOB


FROM EXCH_TYPE A LEFT JOIN 
Mean_spreads_LOB B
ON A.MARKET = B.MARKET
LEFT JOIN
Mean_spreads_AUCTION G
ON A.MARKET = G.MARKET
LEFT JOIN 
PCTSUM_TRADES_VOL C
ON A.MARKET = C.MARKET
LEFT JOIN PCTSUM_TRADES_VOL_BYTYPE_T H
ON
A.MARKET = H.MARKET
LEFT JOIN EXCHANGES_LABEL F
ON
A.MARKET = F.MARKET


;
QUIT;


DATA EXCH_DATA;
	SET EXCH_DATA;


	/*nyse america*/
	IF sender="A" or
	/*CBOE*/
	sender="C" or
	/*Edge*/
	sender="E" or
	/*ice*/
	sender="I" or
	/*ARCA*/
	sender="N" or
	/*MIAX*/
	sender="M" or
	/*PHLX*/
	sender="X"
	THEN M_TYPE=PUT("PFOF",$32.);
	ELSE M_TYPE=PUT("MT",$32.);

RUN;




data EXCH_DATA;
	set EXCH_DATA ;

	MARKET_Char = put(MARKET, $32.); 
	PERC_VOL_Char = put(PERC_VOL, 8.2);
	PCTSUM_LOB_CHAR= put(PCTSUM_LOB, 8.2);
	PCTSUM_AUC_CHAR= put(PCTSUM_AUC, 8.2);
	M_TYPE_Char	= put(M_TYPE, $32.); 
	IF AUC_P=0 THEN AUCTION_EXIST=PUT("No",$3.); ELSE AUCTION_EXIST=PUT("Yes",$3.);

	ampher=put("&",$1.);
	slash=put("\\",$2.);

	
	QS_C_LOB_Char= 	put(QS_C_LOB , 8.2);
	ES_C_LOB_Char= 	put(ES_C_LOB , 8.2);



	EQ_LOB_Char= 		put(EQ_LOB , 8.2);
	QS_C_LOB_Char= 		put(QS_C_LOB , 8.2);
	ES_C_LOB_Char= 		put(ES_C_LOB , 8.2);
	PIMPR_C_LOB_Char= 	put(PIMPR_C_LOB , 8.2);



	EQ_AUC_Char= 		put(EQ_AUC , 8.2);
	QS_C_AUC_Char= 		put(QS_C_AUC , 8.2);
	ES_C_AUC_Char= 		put(ES_C_AUC , 8.2);
	PIMPR_C_AUC_Char = 	put(PIMPR_C_AUC , 8.2);





	IF MARKET IN ("NOM","GEMX","PHLX","MRX","ISE","BX") THEN EXCHANGE_GROUP=PUT("NASDAQ",$128.);
	ELSE IF  MARKET IN ("ARCA","AMEX") THEN EXCHANGE_GROUP=PUT("ICE",$128.);
	ELSE IF MARKET IN ("BATS","CBOE","Cboe C2","EDGX") THEN EXCHANGE_GROUP=PUT("CBOE",$128.);
	ELSE IF MARKET IN ("EMERALD","PEARL","MIAX","EDGX") THEN EXCHANGE_GROUP=PUT("MIAMI",$128.);
	ELSE IF MARKET IN ("BOX") THEN EXCHANGE_GROUP=PUT("TMX",$128.);

	IF DMMP_IND=1 THEN DOF_Char	= put("DMMP", $32.);  ELSE DOF_Char	= put("Non-DMMP", $32.);






	run;




PROC SORT DATA=EXCH_DATA; BY  DOF_CHAR DESCENDING PERC_VOL ;RUN;


ODS GRAPHICS OFF;
ODS RESULTS;
ODS SELECT ALL;
ODS TITLE "TABLE 3: Exchange Summary (Pseudo Data)";
FOOTNOTE "This table presents statistics on option exchanges using pseudo OPRA data provided
 online.";
PROC REPORT data=EXCH_DATA;
 COLUMNS


	MARKET_Char 
	EXCHANGE_GROUP
	PERC_VOL_Char 
	PCTSUM_LOB_CHAR
	PCTSUM_AUC_CHAR	
	DOF_Char
	M_TYPE
	AUCTION_EXIST;

 DEFINE MARKET_Char   / DISPLAY "Exchange" format =$168.;
 DEFINE EXCHANGE_GROUP /DISPLAY "Group" format =$168.;
 DEFINE PERC_VOL_Char  / DISPLAY "Volume" format =$168.;
 DEFINE PCTSUM_LOB_CHAR  / DISPLAY "LOB Volume" format =$168.;
 DEFINE DOF_Char  / DISPLAY "Auction Volume" format =$168.;
 DEFINE M_TYPE  / DISPLAY "Model" format =$168.;
 DEFINE AUCTION_EXIST  / DISPLAY "Auctions" format =$168.;

run;
ODS TITLE " ";
FOOTNOTE " ";

PROC SORT DATA=EXCH_DATA; BY  DOF_CHAR DESCENDING PERC_VOL ;RUN;

ODS TITLE "TABLE 4: Option Exchanges and Execution Costs (Pseudo Data)";
FOOTNOTE "This table presents execution cost variables across option exchanges using pseudo OPRA
 data provided online.";
PROC REPORT data=EXCH_DATA;
 COLUMNS



	MARKET_Char 
	QS_C_LOB_Char
	ES_C_LOB_Char
	PIMPR_C_LOB_Char
	EQ_LOB_Char

	QS_C_AUC_Char
	ES_C_AUC_Char
	PIMPR_C_AUC_Char
	EQ_AUC_Char;


 DEFINE MARKET_Char   / DISPLAY "Exchange" format =$168.;
 DEFINE QS_C_LOB_Char /DISPLAY "LOB QS" format =$168.;
 DEFINE ES_C_LOB_Char  / DISPLAY "LOB ES" format =$168.;
 DEFINE PIMPR_C_LOB_Char  / DISPLAY "LOB Price Impr." format =$168.;
 DEFINE EQ_LOB_Char  / DISPLAY "LOB EQ" format =$168.;

 DEFINE QS_C_AUC_Char /DISPLAY "AUC QS" format =$168.;
 DEFINE ES_C_AUC_Char  / DISPLAY "AUC ES" format =$168.;
 DEFINE PIMPR_C_AUC_Char  / DISPLAY "AUC Price Impr." format =$168.;
 DEFINE EQ_AUC_Char  / DISPLAY "AUC EQ" format =$168.;




;
run;

/*=========================================*/
FOOTNOTE " ";
ODS TITLE " ";




proc printto log=log;
run;

